RDBMS SOLUTION
ASSIGNMENT 4
SET A Q.1 SOLUTION
Write a function which will return minimum maturity amount of all policies.
Source code:
/* assignment 4 set a q 1 solution
here first we will create 3 tables that is 3nf database and then solve the 3 queries
creating tables*/
create table cclient
(
client_no number not null primary key,
client_name varchar2(40),
address varchar2(40),
birthdate date
)
insert into cclient values(1,'c1','a1',to_date('12/12/2000','dd/mm/yyyy'))
insert into cclient values(2,'c2','a2',to_date('10/02/1999','dd/mm/yyyy'))
insert into cclient values(3,'c3','a3',to_date('06/01/1974','dd/mm/yyyy'))
insert into cclient values(4,'c4','a4',to_date('02/11/2003','dd/mm/yyyy'))
create table policy_info
(
policy_no number not null primary key,
descr varchar2(40),
maturity_amt number check(maturity_amt>0),
prem_amt number check(prem_amt>0),
pdate date
)
insert into policy_info values(1,'d1',10000,20000,to_date('01,01,2020'))
insert into policy_info values(2,'d2',15000,30000,to_date('01,01,2020'))
insert into policy_info values(3,'d3',20000,40000,to_date('01,02,2020'))
insert into policy_info values(4,'d4',25000,450000,to_date('01,03,2020'))
create table cli_pol
(
client_no number references cclient(client_no),
policy_no number references policy_info(policy_no)
)
insert into cli_pol values(1,1)
insert into cli_pol values(1,2)
insert into cli_pol values(1,3)
insert into cli_pol values(1,4)
insert into cli_pol values(2,2)
insert into cli_pol values(2,1)
insert into cli_pol values(3,3)
/*inserting the records will be done already*/
/*function declaration*/
create or replace function tot_mat(cno in number)
return number is totamt number;
begin
select sum(maturity_amt) into totamt from cclient cc,policy_info pi,cli_pol cp
where cp.client_no=cno and pi.policy_no=cp.policy_no and cc.client_no=cp.client_no;
return totamt;
end;
/*execution*/
declare
cno number:=:client_no;
mat_amt number;
begin
mat_amt:=tot_mat(cno);
dbms_output.put_line(mat_amt||'is the total maturity amount');
end;
0 Comments